﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient

Public Class DatPhongServer
    Public Shared Sub DatPhong(ByVal idKS As Guid, ByVal fromDate As Date, ByVal toDate As Date, ByVal ghiChu As String, ByVal idLoaiPhong As Guid, ByVal soLuongPhong As Integer, ByVal soGP As Integer, ByVal sale_Price As Double, ByVal price As Double, ByVal maBookingIDENTITY As Integer, ByVal nameKH As String, ByVal email As String, ByVal phone As String, ByVal idKM As Guid)
        Try
            Dim idKH As Guid = Guid.NewGuid()
            Dim sqlKH As String = "INSERT INTO [KHACH_HANG]"
            sqlKH &= " ([ID] ,[DELETED] ,[CREATED_DATE] ,[CREATED_USER_ID] ,[MODIFIED_DATE]"
            sqlKH &= " ,[MODIFIED_USER_ID] ,[ASSIGN_USER_ID] ,[NAME] ,[NAME_KD]"
            sqlKH &= " ,[EMAIL] ,[PHONE1] ,[PHONE2] ,[ADDRESS] ,[CONGTY] ,[DIEMTHUONG], [CHUCVU] ,[STT]"
            sqlKH &= " ,[DISPLAY],[AVATAR] ,[THANHVIEN]"
            sqlKH &= " ,[IS_GET_PROMOTION] ,[REGISTERED_FROM] ,[ACCOUNT],[PASSWORD])"
            sqlKH &= " VALUES"
            sqlKH = String.Format("{0} ('{1}'", sqlKH, idKH)
            sqlKH &= " ,'0'"
            sqlKH &= " ,getdate()"
            sqlKH &= " ,NULL"
            sqlKH &= " ,NULL"
            sqlKH &= " ,NULL"
            sqlKH &= " ,NULL"
            sqlKH &= " ,N'" + nameKH + "' "
            sqlKH &= " ,'" + StripDiacritics(nameKH) + "' "
            sqlKH &= " ,'" + email + "' "
            sqlKH &= " ,'" + phone + "' "
            sqlKH &= " ,'' "
            sqlKH &= " ,'' "
            sqlKH &= " ,'' "
            sqlKH &= "  ,NULL "
            sqlKH &= " ,'' "
            sqlKH &= " ,NULL"
            sqlKH &= " ,'1'"
            sqlKH &= " ,NULL"
            sqlKH &= " ,NULL"
            sqlKH &= " ,NULL"
            sqlKH &= " ,NULL"
            sqlKH &= " ,NULL"
            sqlKH &= " ,NULL) "

            'Connection.ExcuteQuery(sqlKH)
            'Connection.Dispose()

            Dim idBooking As Guid = Guid.NewGuid()

            Dim sql As String = "INSERT INTO [BOOKING] "
            sql &= " ([ID],[DELETED],[CREATED_DATE],[CREATED_USER_ID],[MODIFIED_DATE] ,[MODIFIED_USER_ID]"
            sql &= " ,[ASSIGN_USER_ID],[DISPLAY],[KHACH_SAN_ID],[KHACH_HANG_ID]"
            sql &= " ,[TU_NGAY],[DEN_NGAY],[GHI_CHU],[TRANG_THAI]"
            sql &= " ,[GHI_CHU_CUA_NHAN_VIEN],[NGAY_THANH_TOAN],[IS_VIEW] ,[MA_BOOKING] ,[MA_BOOKING_IDENTITY]"
            sql &= " ,[KHUYEN_MAI_ID] ,[SO_LUONG_KHUYEN_MAI] ,[NGAN_HANG],[DIEM_TICH_LUY])"
            sql &= " VALUES "
            sql = String.Format(" {0} ('{1}'", sql, idBooking)
            sql &= " ,'0'"
            sql &= " ,getdate()"
            sql &= " ,NULL"
            sql &= " ,getdate()"
            sql &= " ,'877B04FB-02FA-43F9-A96C-3BBE4FAC7F21' "
            sql &= " ,'877B04FB-02FA-43F9-A96C-3BBE4FAC7F21' "
            sql &= " ,NULL"
            sql = String.Format(" {0}, '{1}'", sql, idKS)
            sql = String.Format(" {0}, '{1}'", sql, idKH)
            sql = String.Format(" {0}, '{1}'", sql, fromDate)
            sql = String.Format(" {0}, '{1}'", sql, toDate)
            sql = String.Format(" {0}, N'{1}'", sql, ghiChu)
            sql &= " ,N'Đặt phòng mới'"
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql = String.Format(" {0}, 'KS24000{1}'", sql, maBookingIDENTITY)
            sql = String.Format(" {0}, '{1}'", sql, maBookingIDENTITY)
            If idKM = Nothing Then
                sql &= " ,NULL"
            Else
                sql = String.Format(" {0}, '{1}'", sql, idKM)
            End If
            sql &= " ,NULL"
            sql &= " ,''"
            sql &= " ,0) "

            Dim sqlKHAndBook As String = String.Format(" {0} {1} ", sqlKH, sql)
            Connection.ExcuteQuery(sqlKHAndBook)
            Connection.Dispose()

            For i As Integer = 0 To soLuongPhong - 1
                Dim sqlChiTiet As String = "INSERT INTO [BOOKING_DETAIL] "
                sqlChiTiet &= " ([ID] ,[BOOKING_ID] ,[LOAI_PHONG_ID],[SO_LUONG]"
                sqlChiTiet &= " ,[DON_GIA] ,[DON_GIA_MUA],[NGAY]"
                sqlChiTiet &= " ,[STT_PHONG] ,[TEN_NGUOI_DAI_DIEN],[PHU_THU]"
                sqlChiTiet &= " ,[SL_GP] ,[LOAI_GIUONG])"
                sqlChiTiet &= "  VALUES"
                sqlChiTiet &= " (NEWID () "
                sqlChiTiet = String.Format(" {0}, '{1}'", sqlChiTiet, idBooking)
                sqlChiTiet = String.Format(" {0}, '{1}'", sqlChiTiet, idLoaiPhong)
                sqlChiTiet = String.Format(" {0}, '{1}'", sqlChiTiet, soLuongPhong)
                sqlChiTiet = String.Format(" {0}, '{1}'", sqlChiTiet, sale_Price)
                sqlChiTiet = String.Format(" {0}, '{1}'", sqlChiTiet, price)
                sqlChiTiet = String.Format(" {0}, '{1}'", sqlChiTiet, fromDate)
                sqlChiTiet = String.Format(" {0}, '{1}'", sqlChiTiet, i + 1)
                sqlChiTiet &= " ,'' "
                sqlChiTiet &= " ,0 "
                sqlChiTiet = String.Format(" {0}, '{1}'", sqlChiTiet, soGP)
                sqlChiTiet &= " ,NULL )"
                Connection.ExcuteQuery(sqlChiTiet)
                Connection.Dispose()
            Next


        Catch ex As Exception
            Connection.CheckCloseConnect()
            ' ''Throw ex
        End Try
    End Sub

    'Public Shared Sub InsertKhachHangMoi(ByVal id As Guid, ByVal nameKH As String, ByVal email As String, ByVal phone As String)
    '    Try
    '        Dim sql As String = "INSERT INTO [KHACH_HANG]"
    '        sql &= " ([ID] ,[DELETED] ,[CREATED_DATE] ,[CREATED_USER_ID] ,[MODIFIED_DATE]"
    '        sql &= " ,[MODIFIED_USER_ID] ,[ASSIGN_USER_ID] ,[NAME] ,[NAME_KD]"
    '        sql &= " ,[EMAIL] ,[PHONE1] ,[PHONE2] ,[ADDRESS] ,[CONGTY] ,[DIEMTHUONG], [CHUCVU] ,[STT]"
    '        sql &= " ,[DISPLAY],[AVATAR] ,[THANHVIEN]"
    '        sql &= " ,[IS_GET_PROMOTION] ,[REGISTERED_FROM] ,[ACCOUNT],[PASSWORD])"
    '        sql &= " VALUES"
    '        sql = String.Format(" ({0}, {1}", sql, id)
    '        sql &= " ,'0'"
    '        sql &= " ,getdate()"
    '        sql &= " ,NULL"
    '        sql &= " ,NULL"
    '        sql &= " ,NULL"
    '        sql &= " ,NULL"
    '        sql &= " ,'" + nameKH + "' "
    '        sql &= " ,'" + StripDiacritics(nameKH) + "' "
    '        sql &= " ,'" + email + "' "
    '        sql &= " ,'" + phone + "' "
    '        sql &= " ,'' "
    '        sql &= " ,'' "
    '        sql &= " ,'' "
    '        sql &= "  ,NULL "
    '        sql &= " ,'' "
    '        sql &= " ,NULL"
    '        sql &= " ,'1'"
    '        sql &= " ,NULL"
    '        sql &= " ,NULL"
    '        sql &= " ,NULL"
    '        sql &= " ,NULL"
    '        sql &= " ,NULL"
    '        sql &= " ,NULL) "
    '        Connection.ExcuteQuery(sql)
    '        Connection.Dispose()
    '    Catch ex As Exception
    '        Connection.CheckCloseConnect()
    '        ''Throw ex
    '    End Try
    'End Sub

    Public Shared Function StripDiacritics(accented As String) As String
        Dim regex As New Regex("\p{IsCombiningDiacriticalMarks}+")
        Dim strFormD As String = accented.Normalize(NormalizationForm.FormD)
        Return regex.Replace(strFormD, [String].Empty).Replace("đ"c, "d"c).Replace("Đ"c, "D"c)
    End Function

    Public Shared Function GetMaBookingTop() As Integer
        Dim maBooking As Integer = 0
        Try
            Dim sql As String = "SELECT TOP 1 isNull([MA_BOOKING_IDENTITY],0) MA_BOOKING_IDENTITY FROM [BOOKING] order by MA_BOOKING_IDENTITY desc"
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                maBooking = reader("MA_BOOKING_IDENTITY")
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return maBooking
    End Function

    Public Shared Sub InsertDanhGia(ByVal idKS As Guid, ByVal tieuDe As String, ByVal content As String, ByVal fullName As String, ByVal email As String, ByVal rate As Integer)
        Try
            Dim sql As String = " INSERT INTO [DANH_GIA]"
            sql &= " ([ID] ,[DELETED] ,[CREATED_DATE] ,[CREATED_USER_ID] ,[MODIFIED_DATE]"
            sql &= " ,[MODIFIED_USER_ID] ,[KHACH_SAN_ID] ,[DISPLAY] ,[TIEU_DE]  ,[CONTENT]"
            sql &= " ,[FULL_NAME] ,[EMAIL]  ,[SO_NGAY_LUU_TRU],[RATE],[ORDER]"
            sql &= " ,[IMG_LIST],[VIDEO_LIST] ,[CONTENT2])"
            sql &= " VALUES "
            sql &= " (NEWID ()"
            sql &= " ,'0'"
            sql &= " ,getdate()"
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql = String.Format(" {0}, '{1}'", sql, idKS)
            sql &= " ,'0'"
            sql &= " ,'" + tieuDe + "' "
            sql &= " ,'" + content + "' "
            sql &= " ,'" + fullName + "' "
            sql &= " ,'" + email + "' "
            sql &= "  ,NULL"
            sql &= " ,'" & rate & "' "
            sql &= " ,0 "
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql &= " ,NULL)"

            Connection.ExcuteQuery(sql)
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
    End Sub

    Public Shared Function GetListDanhGiaByIDHotel(ByVal id As Guid, Optional ByVal indexStar As Integer = 0) As List(Of DanhGia)
        Dim listDanhGia As New List(Of DanhGia)
        Try
            Dim sql As String = "SELECT * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY CREATED_DATE desc) as row, [ID], [CREATED_DATE],[KHACH_SAN_ID],[DISPLAY]"
            sql &= " ,[TIEU_DE] ,[CONTENT] ,[FULL_NAME],[EMAIL],[RATE]"
            sql &= "FROM [DANH_GIA]"
            sql = String.Format(" {0} where DELETED ='0' and DISPLAY='1' and KHACH_SAN_ID='{1}'", sql, id)
            sql &= String.Format("  ) b WHERE b.row > {0} and b.row <= {1}", indexStar * Util.GetConfigKey("page_num"), (indexStar + 1) * Util.GetConfigKey("page_num"))
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim danhGia As New DanhGia
                danhGia.ID = reader("ID")
                danhGia.CREATED_DATE = reader("CREATED_DATE")
                danhGia.KHACH_SAN_ID = reader("KHACH_SAN_ID")
                danhGia.DISPLAY = reader("DISPLAY")
                danhGia.TIEU_DE = reader("TIEU_DE")
                danhGia.CONTENT = reader("CONTENT")
                danhGia.FULL_NAME = reader("FULL_NAME")
                danhGia.EMAIL = reader("EMAIL")
                danhGia.RATE = reader("RATE")

                listDanhGia.Add(danhGia)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return listDanhGia
    End Function
    Public Shared Function GetCountDanhGiaByIDHotel(ByVal id As Guid) As Integer
        Dim num As New Integer
        Try
            Dim sql As String = "SELECT  count(*) as num "
            sql &= "FROM [DANH_GIA]"
            sql = String.Format(" {0} where DELETED ='0' and DISPLAY='1' and KHACH_SAN_ID='{1}'", sql, id)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                num = reader("num")
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return num
    End Function
    Public Shared Function IsExistsTrip(ByVal maBooking As String) As Boolean
        Dim isExists As Boolean = False
        Try
            Dim sql As String = String.Format("select MA_BOOKING from BOOKING where MA_BOOKING = '{0}'", maBooking)
            Using conn As New SqlConnection(Connection.ConnectionString)
                conn.Open()
                Dim cmd As New SqlCommand(sql, conn)
                Using reader As SqlDataReader = cmd.ExecuteReader()
                    If reader.Read AndAlso reader.HasRows Then
                        isExists = True
                    End If
                End Using
                cmd.Dispose()
            End Using
        Catch ex As Exception
            Connection.CheckCloseConnect()
        End Try
        Return isExists
    End Function

    Public Shared Function IsExistsMaBookingKhuyenMai(ByVal maBooking As String) As Boolean
        Dim isExists As Boolean = False
        Try
            Dim sql As String = String.Format("select MA_BOOKING from BOOKING where MA_BOOKING = '{0}' and KHUYEN_MAI_ID is not null ", maBooking)
            Using conn As New SqlConnection(Connection.ConnectionString)
                conn.Open()
                Dim cmd As New SqlCommand(sql, conn)
                Using reader As SqlDataReader = cmd.ExecuteReader()
                    If reader.Read AndAlso reader.HasRows Then
                        isExists = True
                    End If
                End Using
                cmd.Dispose()
            End Using
        Catch ex As Exception
            Connection.CheckCloseConnect()
        End Try
        Return isExists
    End Function

    Public Shared Function GetBookingByMaBooking(ByVal maBooking As String) As Dictionary(Of String, Object)
        Dim dictObjectBooking As New Dictionary(Of String, Object)
        Try
            Dim sql As String = String.Format("SELECT  distinct * FROM [BOOKING] where MA_BOOKING = '{0}'", maBooking)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
                Util.SetDictionaryValueByKey(dictObjectBooking, "PID", (reader("PID")))
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return dictObjectBooking
    End Function

    Public Shared Sub InsertFeedBack(ByVal email As String, ByVal phone As String, ByVal nameKS As String, ByVal content As String)
        Try
            Dim sql As String = "INSERT INTO [FeedBack] ([FName] ,[FEmail],[FAdd] ,[FPhone]"
            sql &= " ,[FTitle] ,[FContent] ,[FDate],[FCompanyName]"
            sql &= " ,[FStars],[FCity] ,[FCharge],[FIsPartner],[FType],[FStatus])"
            sql &= "  VALUES"
            sql &= " (N'Người dùng website'"
            sql &= " ,'" + email + "'"
            sql &= " ,NULL"
            sql &= " ,'" + phone + "'"
            sql &= " ,N'Đăng ký lấy giá phòng " + nameKS + "'"
            sql &= " ,N'" + content + "'"
            sql &= " ,Getdate()"
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql &= " ,NULL"
            sql &= " ,'GetPrice'"
            sql &= " ,NULL)"
            Connection.ExcuteQuery(sql)
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
    End Sub
End Class
